package com.delistry.database;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.delistry.database.schema.AccountDelistyTable;
import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DatabaseFactory {
	/** 紀錄用 */
	private static Logger _log = Logger.getLogger(DatabaseFactory.class
			.getName());

	/** 資料庫的實例 */
	private static DatabaseFactory _instance;

	/** 資料庫連結的來源 */
	private ComboPooledDataSource _source;

	/**
	 * 設定資料庫登入的相關資訊
	 * 
	 * @param driver
	 *            資料庫連結的驅動程式
	 * @param url
	 *            資料庫連結的位址
	 * @param user
	 *            登入資料庫的使用者名稱
	 * @param password
	 *            登入資料庫的密碼
	 */
	/** 綱要表名稱 Schema Name */
	private static String DB_SCHEMA = "delistry";
	/** 資料庫驅動 */
	private static String DB_DRIVER = "com.mysql.jdbc.Driver";
	/** 資料庫位址 */
	private static String DB_URL = "jdbc:mysql://127.0.0.1/" + DB_SCHEMA
			+ "?useUnicode=true&characterEncoding=utf8";
	/** 資料庫帳號 */
	private static String DB_LOGIN = "root";
	/** 資料庫密碼 */
	private static String DB_PASSWORD = "1234";

	/**
	 * 取得資料庫的實例（第一次實例為 null 的時候才新建立一個).
	 * 
	 * @return DatabaseFactory
	 * @throws SQLException
	 */
	public static DatabaseFactory getInstance() throws SQLException {
		if (_instance == null) {
			_instance = new DatabaseFactory();
		}
		return _instance;
	}

	public static void InitialDBTable() {
		AccountDelistyTable.getInstance();
	}

	/**
	 * 資料庫連結的設定與配置
	 * 
	 * @throws SQLException
	 */
	public DatabaseFactory() throws SQLException {
		try {
			_source = new ComboPooledDataSource();
			_source.setAutoCommitOnClose(true);
			_source.setInitialPoolSize(10);
			_source.setMinPoolSize(10);
			_source.setDriverClass(DB_DRIVER);
			_source.setJdbcUrl(DB_URL);
			_source.setUser(DB_LOGIN);
			_source.setPassword(DB_PASSWORD);

			/* Test the connection */
			_source.getConnection().close();
		} catch (SQLException x) {
			_log.fine("Database Connection FAILED");
			// rethrow the exception
			throw x;
		} catch (Exception e) {
			_log.fine("Database Connection FAILED");
			throw new SQLException("could not init DB connection:" + e);
		}
	}

	/**
 	*/
	public void shutdown() {
		try {
			_source.close();
		} catch (Exception e) {
			_log.log(Level.INFO, "", e);
		}
		try {
			_source = null;
		} catch (Exception e) {
			_log.log(Level.INFO, "", e);
		}
	}

	/**
	 * 取得資料庫連結時的連線
	 * 
	 * @return Connection 連結對象
	 * @throws SQLException
	 */
	public Connection getConnection() {
		Connection con = null;

		while (con == null) {
			try {
				con = _source.getConnection();
			} catch (SQLException e) {
				_log.warning("DatabaseFactory: getConnection() failed, trying again "
						+ e);
			}
		}
		return con;
	}

}
